﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Agresso.Interface.Layer.SQL
{
    public class BunkerGRNSQL
    {
        public static string GetAllBunkerGRN()
        {
            return @"   
                    Select distinct
                        vVMVoyageSrch.VesselName vessel_name,
                        vVMVoyageSrch.VoyageNo voyage_no,
                        vLiftingSrch.StemmedDate stemmed_Date,
                        vSupplierSrch.SupplierName supplier,
                        vBSEnquirySearch.EnquiryID enquiry_no,
                        vLiftingSrch.LiftingStatus lifting_status,
                        vLiftingSrch.Quality grade,
                        vLiftingSrch.OperatorActual quantity_delivered,
                        vSupplierSrch.DeliveryDate delivery_Date,
                        vBSEnquirySearch.EnquiryDate enquiry_Date
                         FROM VVMVOYAGESRCH 
                         JOIN VBSENQUIRYSEARCH ON VBSENQUIRYSEARCH.VMVOYAGE = VVMVOYAGESRCH.ID 
                         JOIN VVMVOYAGEPORTSRCH ON VVMVOYAGESRCH.ID=VVMVOYAGEPORTSRCH.VOYAGE AND 
                         VBSENQUIRYSEARCH.VMVOYAGEPORT = VVMVOYAGEPORTSRCH.ID 
                         JOIN VDEPTORGANISATIONSRCH ON VVMVOYAGESRCH.ID=VDEPTORGANISATIONSRCH.ID 
                         JOIN DELTAVIEWPERMSETTING ON VVMVOYAGESRCH.COMPANY=DELTAVIEWPERMSETTING.DEPT 
                         JOIN VBSENQUIRYBUNKERLIFTING ON VBSENQUIRYSEARCH.ENQUIRYID = VBSENQUIRYBUNKERLIFTING.ENQUIRY 
                         JOIN VSUPPLIERSRCH ON VVMVOYAGEPORTSRCH.ID = VSUPPLIERSRCH.PORT 
                         JOIN VLIFTINGSRCH ON VSUPPLIERSRCH.ID = VLIFTINGSRCH.VMVOYAGEPORTBUNKERSUPPLIER AND 
                         VLIFTINGSRCH.ID = VBSENQUIRYBUNKERLIFTING.VMVOYAGEPORTBUNKERLIFTING 
                         WHERE vVMVoyageSrch.Category NOT LIKE 'UnFixed' AND 
                         (vVMVoyageSrch.VesselName is NOT NULL) 
                         AND (vLiftingSrch.LiftingStatus = 'Delivered')
                         --AND vBSEnquirySearch.EnquiryDate > '2013-07-23 00:00' 
                         AND (vBSEnquiryBunkerLifting.StemDate > '2013-07-23 00:00' 
OR vBSEnquirySearch.EnquiryID in 
(
'766',
'779',
'781',
'781',
'788',
'784',
'792',
'763',
'765',
'775',
'760',
'776',
'778',
'786',
'786',
'761',
'847',
'767',
'771',
'780',
'794',
'796',
'782',
'852',
'856',
'860',
'886'
)) 
            "; //request additional old GRN
        }
        public static string GetAllBunkerGRNByEnquiryID()
        {
            return @"   
                    Select distinct
                        vVMVoyageSrch.VesselName vessel_name,
                        vVMVoyageSrch.VoyageNo voyage_no,
                        vLiftingSrch.StemmedDate stemmed_Date,
                        vSupplierSrch.SupplierName supplier,
                        vBSEnquirySearch.EnquiryID enquiry_no,
                        vLiftingSrch.LiftingStatus lifting_status,
                        vLiftingSrch.Quality grade,
                        vLiftingSrch.OperatorActual quantity_delivered,
                        vSupplierSrch.DeliveryDate delivery_Date,
                        vBSEnquirySearch.EnquiryDate enquiry_Date
                         FROM VVMVOYAGESRCH 
                         JOIN VBSENQUIRYSEARCH ON VBSENQUIRYSEARCH.VMVOYAGE = VVMVOYAGESRCH.ID 
                         JOIN VVMVOYAGEPORTSRCH ON VVMVOYAGESRCH.ID=VVMVOYAGEPORTSRCH.VOYAGE AND 
                         VBSENQUIRYSEARCH.VMVOYAGEPORT = VVMVOYAGEPORTSRCH.ID 
                         JOIN VDEPTORGANISATIONSRCH ON VVMVOYAGESRCH.ID=VDEPTORGANISATIONSRCH.ID 
                         JOIN DELTAVIEWPERMSETTING ON VVMVOYAGESRCH.COMPANY=DELTAVIEWPERMSETTING.DEPT 
                         JOIN VBSENQUIRYBUNKERLIFTING ON VBSENQUIRYSEARCH.ENQUIRYID = VBSENQUIRYBUNKERLIFTING.ENQUIRY 
                         JOIN VSUPPLIERSRCH ON VVMVOYAGEPORTSRCH.ID = VSUPPLIERSRCH.PORT 
                         JOIN VLIFTINGSRCH ON VSUPPLIERSRCH.ID = VLIFTINGSRCH.VMVOYAGEPORTBUNKERSUPPLIER AND 
                         VLIFTINGSRCH.ID = VBSENQUIRYBUNKERLIFTING.VMVOYAGEPORTBUNKERLIFTING 
                         WHERE vVMVoyageSrch.Category NOT LIKE 'UnFixed' AND 
                         (vVMVoyageSrch.VesselName is NOT NULL) 
                         AND (vLiftingSrch.LiftingStatus = 'Delivered')
                         and vBSEnquirySearch.EnquiryID =@EnquiryID
            "; //request additional old GRN
        }

        public static string CountBunkerGRNTemp()
        {
            return @"
            select count(*) from i_bunker_grn_temp where 
            vessel_name = @vessel_name
	        and voyage_no = @voyage_no
	        and stemmed_date = @stemmed_date
	        and supplier = @supplier
	        and enquiry_no  = @enquiry_no
	        and lifting_status = @lifting_status
	        and grade = @grade
	        and quantity_delivered = @quantity_delivered
	        and delivery_Date = @delivery_Date
	        and enquiry_Date = @enquiry_Date
            ";
        }

        public static string CountApodetailTemp()
        {
            return @"
            select count(*) from i_apodetail where 
            order_id = @order_id
            ";
        }
        public static string GetApodetailTempByOrderIDSupArticle()
        {
            return @"
            select * from i_apodetail where 
            order_id = @order_id and ext_ord_ref=@ext_ord_ref and line_no=@line_no and sup_article=@sup_article and client=@client
            ";
        }

        public static string InsertBunkerGRNTemp()
        {
            return @"
            INSERT INTO [i_bunker_grn_temp]
           ([vessel_name]
           ,[voyage_no]
           ,[stemmed_date]
           ,[supplier]
           ,[enquiry_no]
           ,[lifting_status]
           ,[grade]
           ,[quantity_delivered]
           ,[delivery_Date]
           ,[enquiry_Date]
           ,[pickup_flag]
           ,[insert_date]
           )
     VALUES
           ( @vessel_name
           , @voyage_no
           , @stemmed_date
           , @supplier
           , @enquiry_no
           , @lifting_status
           , @grade
           , @quantity_delivered
           , @delivery_Date
           , @enquiry_Date
           , @pickup_flag
           , @insert_date
            )";
        }

        public static string GetAllPendingBunkerGRNTemp()
        {
            return @"select * from i_bunker_grn_temp where pickup_flag = '0' order by enquiry_no";
        }

        public static string GetAllBunkerGRNTempByEnquiryNo()
        {
            return @"select * from i_bunker_grn_temp where pickup_flag = '0' and enquiry_no=@enquiry_no and grade=@grade";
        }

        public static string GetAllBunkerGRNTempByEnquiryNoValidStatus()
        {
            return @"select * from i_bunker_grn_temp where pickup_flag = '1' and enquiry_no=@enquiry_no and grade=@grade";
        }

        public static string UpdateGRNTempPickup_flagStatus()
        {
            return @"update i_bunker_grn_temp
            set 
            pickup_flag = @pickup_flag 
            , pickup_date = @pickup_date 
            , pickup_update_date=@pickup_update_date
            , agrtidList = @agrtidList
            , remarks = @remarks
            , batch_id = @batch_id
            where i_bunkergrn_id=@i_bunkergrn_id
               ";
        }

        public static string UpdatePOTempPickup_flagStatus()
        {
            return @"update i_apodetail
            set 
            pickup_flag = @pickup_flag 
            , pickup_date = @pickup_date 
            , remarks = @remarks
            , batch_id = @batch_id
            where i_apodetail_id=@i_apodetail_id
               ";
        }

        public static string GetAllPendingPendingBunkerGRN()
        {
            return @"select distinct(enquiry_no), enquiry_date, convert(char, pickup_date, 101) pickup_date, remarks from i_bunker_grn_temp where pickup_flag = 0  order by enquiry_date";
        }

        public static string GetAllPendingPendingPendingPO()
        {
            return @"select distinct order_id, ext_ord_ref, sup_article, status, convert(char, pickup_date, 101) pickup_date, remarks, art_descr, client from i_apodetail where pickup_flag = 0   and status <> 'C' ";
        }

        public static string GetAllPendingPendingPendingPOCount()
        {
            return @"select count(*) from i_apodetail where pickup_flag = 0";
        }

        public static string GetAllPendingPendingBunkerGRN(string p)
        {
            return string.Format(@"select distinct(enquiry_no), enquiry_date, convert(char, pickup_date, 101) pickup_date, remarks 
                from i_bunker_grn_temp where pickup_flag = 0  
                and convert(char,enquiry_no) +  replace(convert(char(11), enquiry_date, 106),' ','-')+ replace(convert(char(11), pickup_date, 106),' ','-')+ remarks like '%{0}%'
                order by enquiry_date", p);
        }

        public static string GetAllPendingPendingPendingPO(string p)
        {
            return string.Format(@"select distinct order_id, ext_ord_ref, sup_article, status, convert(char, pickup_date, 101) pickup_date, remarks, art_descr ,client
                from i_apodetail where pickup_flag = 0  and status <> 'C' and convert(char,order_id)+ext_ord_ref +sup_article+status+replace(convert(char(11), pickup_date, 106),' ','-')+ remarks like '%{0}%' ", p);
        }

        public static string InsertApodetailTemp()
        {
            return @"
            INSERT INTO i_apodetail
           ([order_id]
           ,[ext_ord_ref]
           ,[line_no]
           ,[sup_article]
           ,[client]
           ,[pickup_flag]
           ,[insert_date]
           ,[status]
            ,[art_descr])
     VALUES
           (@order_id
           ,@ext_ord_ref
           ,@line_no
           ,@sup_article
           ,@client
           ,@pickup_flag
           ,@insert_date
           ,@status
            ,@art_descr
            )
            ";
        }

        public static string GetAllPendingPOTemp()
        {
            return @"select * from i_apodetail where pickup_flag = 0 and status <> 'C' order by order_id";
        }

        public static string GetAllPendingPOTempByOrderID()
        {
            return @"select * from i_apodetail where order_id = @order_id and status <> 'C' and client=@client order by order_id";
        }

        public static string UpdateApodetailStatus()
        {
            return @"update i_apodetail set status=@status where i_apodetail_id = @i_apodetail_id";
        }
        public static string UpdateApodetailArt_Desc()
        {
            return @"update i_apodetail set art_descr=@art_descr where i_apodetail_id = @i_apodetail_id";
        }

        public static string GetAllBunkerGRNEnquiry()
        {
            return "select distinct enquiry_no, grade  from i_bunker_grn_temp where pickup_flag in (0,1,2)";
        }
    }
}
